package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import chapter06.Student;

public class MySQLStudentDao extends BaseDao implements StudentDao {

	@Override
	public Student login(int stuno, String pass) {
		String sql = "select * from student where stuno=? and `password`=?";
		Connection conn = super.getConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = null;
		try {
			st = conn.prepareStatement(sql);
			st.setInt(1, stuno);
			st.setString(2, pass);
			rs = st.executeQuery();
			if (rs.next()) {
				s = new Student();
				s.setStuno(stuno);
				s.setName(rs.getString("sname"));
				s.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.close(conn, st, rs);
		}

		return s;
	}

	@Override
	public Student Register(String name, String sex, int gradeid, String pass) {
		String sql = "insert into student(sname,sex,gradeid,`password`) values(?,?,?,?)";
		Connection conn = super.getConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = null;
		try {
			st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			st.setString(1, name);
			st.setString(2, sex);
			st.setInt(3, gradeid);
			st.setString(4, pass);
			int i = st.executeUpdate();
			rs = st.getGeneratedKeys();
			int stuno = 0;
			if (rs.next()) {
				stuno = rs.getInt(1);
			}
			if (i == 1) {
				s = new Student();
				s.setStuno(stuno);
				s.setName(name);
				s.setPassword(pass);
				s.setGradeid(gradeid);
				s.setSex(sex);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.close(conn, st, rs);
		}

		return s;
	}

	@Override
	public Student alter(int stuno, String name, String sex, int gradeid,
			String pass) {
		String sql = "update student set sname=?,sex=?,gradeid=?,`password`=? where stuno=? ";
		Connection conn = super.getConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = null;
		try {
			st = conn.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, sex);
			st.setInt(3, gradeid);
			st.setString(4, pass);
			st.setInt(5, stuno);
			int i = st.executeUpdate();
			if (i == 1) {
				s = new Student();
				s.setStuno(stuno);
				s.setName(name);
				s.setPassword(pass);
				s.setGradeid(gradeid);
				s.setSex(sex);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.close(conn, st, rs);
		}

		return s;
	}

	@Override
	public Student addScore(int stuno, int subjectId, int score) {
		String sql = "insert into examresult(score,st_id,sb_id) values(?,?,?)";
		Connection conn = super.getConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = null;
		try {
			st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			st.setInt(1, score);
			st.setInt(2, stuno);
			st.setInt(3, subjectId);
			int i = st.executeUpdate();
			if (i == 1) {
				s = new Student();
				s.setStuno(stuno);
				s.setScore(score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.close(conn, st, rs);
		}

		return s;
	}

	@Override
	public List<Student> find(int pageNo, int pageSize) {
		String sql = "select * from student limit ?,?";
		Connection conn = super.getConn();
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = null;
		List<Student> list = new ArrayList<Student>();
		try {
			st = conn.prepareStatement(sql);
			st.setInt(1, pageSize * (pageNo - 1));
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setName(rs.getString("sname"));
				s.setPassword(rs.getString("password"));
				s.setSex(rs.getString("sex"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.close(conn, st, rs);
		}

		return list;
	}

}
